package com.glowdayz.glowmee.bc;

import java.io.IOException;
import java.net.URLEncoder;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.RequestDispatcher;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.glowdayz.common.DBHandler;
import com.glowdayz.common.MultipartBox;
import com.glowdayz.common.PageHandler;
import com.glowdayz.common.Util;
import com.glowdayz.common.MultipartBox.FileInfo;
import com.glowdayz.glowmee.cmd.ProductCMD;
import com.glowdayz.glowmee.cmd.ProductCategoryCMD;
import com.glowdayz.glowmee.dao.BrandDAO;
import com.glowdayz.glowmee.dao.FirstCategoryDAO;
import com.glowdayz.glowmee.dao.ProductDAO;

public class ListProductBC extends HttpServlet {
	
	private static final long serialVersionUID = 1L;
       
    public ListProductBC() {
        super();
    }

	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		DBHandler.getInstance().connect();
		
		request.setCharacterEncoding("utf-8");
		String where = request.getParameter("where");
		if (where != null) {
			where = Util.getInstance().incoding(where);
		} else {
			where = "";
		}
		
		String productTitle = request.getParameter("productTitle");
		String productText = request.getParameter("productText");
		String firstCategory = request.getParameter("firstCategory");
		String secondCategory = request.getParameter("secondCategory");
		String price1 = request.getParameter("price1");
		String price2 = request.getParameter("price2");
		String brand = request.getParameter("brand");
		if (brand != null) {
			brand = Util.getInstance().incoding(brand);
		}
		String keyWord = request.getParameter("keyWord");
		String create_date = request.getParameter("create_date");
		String end_date = request.getParameter("end_date");
		
		String modified_date1 = request.getParameter("modified_date1");
		String modified_date2 = request.getParameter("modified_date2");
		
		
		if (productTitle != null && !productTitle.equals("")) {
			productTitle = Util.getInstance().incoding(productTitle);
			where += " AND (replace(productTitle,' ','') like '%" + productTitle + "%' OR productTitle like '%" + productTitle + "%')";
		}
		
		if (productText != null && !productText.equals("")) {
			productText = Util.getInstance().incoding(productText);
			where += " AND (replace(productText,' ','') like '%" + productText + "%' OR productText like '%" + productText + "%')";
		}
		
		if (secondCategory != null && !secondCategory.equals("default")) {
			secondCategory = Util.getInstance().incoding(secondCategory);
			where += " AND p.idProduct IN (SELECT idProduct FROM secondcategorymapping WHERE idSecondCategory=" + secondCategory + ")";
		} else if (firstCategory != null && !firstCategory.equals("default")) {
			firstCategory = Util.getInstance().incoding(firstCategory);
			where += " AND p.idProduct IN (SELECT DISTINCT sm.idProduct FROM secondcategorymapping sm, secondcategory se, firstcategory fi "
					+ "WHERE fi.idFirstCategory = se.idFirstCategory AND se.idSecondCategory = sm.idSecondCategory AND fi.idFirstCategory="
					+ firstCategory + ")";
		}
		
		if (price1 != null && !price1.equals("") && price2 != null && !price2.equals("")) {
			where += " AND price BETWEEN " + price1 + " AND " + price2;
		} else if (price1 != null && !price1.equals("")) {
			where += " AND price >= " + price1;
		} else if (price2 != null && !price2.equals("")) {
			where += " AND price <= " + price2;
		}
		if (brand != null && !brand.equals("--- 선택하세요 ---")) {
			where += " AND p.idBrand = " + brand;
		}
		if (keyWord != null && !keyWord.equals("")) {
			keyWord = Util.getInstance().incoding(keyWord);
			where += " AND keyWord like '%" + keyWord + "%'";
		}
		if (create_date != null && !create_date.equals("") && end_date != null && !end_date.equals("")) {
			request.setAttribute("searchCreate_date", create_date);
			request.setAttribute("searchEnd_date", end_date);
			create_date = create_date.replace("-", "");
			create_date += "000000";
			end_date += "999999";
			end_date = end_date.replace("-", "");
			where += " AND p.create_date BETWEEN " + create_date + " AND " + end_date;
		} else if (create_date != null && !create_date.equals("")) {
			request.setAttribute("searchCreate_date", create_date);
			create_date = create_date.replace("-", "");
			create_date += "000000";
			where += " AND p.create_date > " + create_date;
		} else if (end_date != null && !end_date.equals("")) {
			request.setAttribute("searchEnd_date", end_date);
			end_date += "999999";
			end_date = end_date.replace("-", "");
			where += " AND p.create_date < " + end_date;
		}
		
		if (modified_date1 != null && !modified_date1.equals("") && modified_date2 != null && !modified_date2.equals("")) {
			request.setAttribute("searchModified_date1", modified_date1);
			request.setAttribute("searchModified_date2", modified_date2);
			modified_date1 = modified_date1.replace("-", "");
			modified_date1 += "000000";
			modified_date2 += "999999";
			modified_date2 = modified_date2.replace("-", "");
			where += " AND p.modified_date BETWEEN " + modified_date1 + " AND " + modified_date2;
		} else if (modified_date1 != null && !modified_date1.equals("")) {
			request.setAttribute("searchModified_date1", modified_date1);
			modified_date1 = modified_date1.replace("-", "");
			modified_date1 += "000000";
			where += " AND p.modified_date > " + modified_date1;
		} else if (modified_date2 != null && !modified_date2.equals("")) {
			request.setAttribute("searchModified_date2", modified_date2);
			modified_date2 += "999999";
			modified_date2 = modified_date2.replace("-", "");
			where += " AND p.modified_date < " + modified_date2;
		}
		
		request.setAttribute("searchProductTitle", productTitle);
		request.setAttribute("searchProductText", productText);
		if (firstCategory != null && !firstCategory.equals("default")) request.setAttribute("searchFirstCategory", firstCategory);
		if (secondCategory != null && !secondCategory.equals("default")) request.setAttribute("searchSecondCategory", secondCategory);
		request.setAttribute("searchPrice1", price1);
		request.setAttribute("searchPrice2", price2);
		
		if (brand != null && !brand.equals("--- 선택하세요 ---")) {
			request.setAttribute("searchBrand", brand);
		} else {
			int idBrand = 0;
			String sql = "SELECT idBrand FROM product ORDER BY create_date DESC LIMIT 0, 1";
			PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
			try {
				ResultSet rs = pstmt.executeQuery();
				if (rs != null) {
					if (rs.next()) {
						idBrand = rs.getInt(1);
					}
					rs.close();
					rs = null;
				}
				pstmt.close();
				pstmt = null;
			} catch (SQLException e) {}
			request.setAttribute("searchBrand", idBrand);
		}
		request.setAttribute("searchKeyWord", keyWord);
		
		String order = request.getParameter("order");
		request.setAttribute("sort", order);
		
		if (order != null && order.equals("isUnDisplay")) {
			order = "p.isDisplay ASC, p.idProduct DESC";
			request.setAttribute("order", "&order=isUnDisplay");
		} else if (order != null && order.equals("isDisplay")) {
			order = "p.isDisplay DESC, p.idProduct DESC";
			request.setAttribute("order", "&order=isDisplay");
		}
		
		if (order != null && order.equals("isUnBrandTitle")) {
			order = "b.brandTitle ASC, p.idProduct DESC";
			request.setAttribute("order", "&order=isUnBrandTitle");
		} else if (order != null && order.equals("isBrandTitle")) {
			order = "b.brandTitle DESC, p.idProduct DESC";
			request.setAttribute("order", "&order=isBrandTitle");
		}
		
		if (order != null && order.equals("isUnImg")) {
			order = "p.productImg ASC, p.idProduct DESC";
			request.setAttribute("order", "&order=isUnImg");
		} else if (order != null && order.equals("isImg")) {
			order = "p.productImg DESC, p.idProduct DESC";
			request.setAttribute("order", "&order=isImg");
		}
		
		if (order != null && order.equals("isUnReadCount")) {
			order = "p.readCount ASC, p.idProduct DESC";
			request.setAttribute("order", "&order=isUnReadCount");
		} else if (order != null && order.equals("isReadCount")) {
			order = "p.readCount DESC, p.idProduct DESC";
			request.setAttribute("order", "&order=isReadCount");
		}
		
		if (order != null && order.equals("isUnWishCount")) {
			order = "count1 ASC, p.idProduct DESC";
			request.setAttribute("order", "&order=isUnWishCount");
		} else if (order != null && order.equals("isWishCount")) {
			order = "count1 DESC, p.idProduct DESC";
			request.setAttribute("order", "&order=isWishCount");
		}
		
		if (order != null && order.equals("isUnPouchCount")) {
			order = "count2 ASC, p.idProduct DESC";
			request.setAttribute("order", "&order=isUnPouchCount");
		} else if (order != null && order.equals("isPouchCount")) {
			order = "count2 DESC, p.idProduct DESC";
			request.setAttribute("order", "&order=isPouchCount");
		}
		
		if (order != null && order.equals("isUnCommentCount")) {
			order = "count3 ASC, p.idProduct DESC";
			request.setAttribute("order", "&order=isUnCommentCount");
		} else if (order != null && order.equals("isCommentCount")) {
			order = "count3 DESC, p.idProduct DESC";
			request.setAttribute("order", "&order=isCommentCount");
		}
		
		if (order != null && order.equals("isProductScore")) {
			order = "p.productScore DESC";
			request.setAttribute("order", "&order=isProductScore");
		}
		
		if (order != null && order.equals("isCreate_date")) {
			order = "p.create_date DESC";
			request.setAttribute("order", "&order=isCreate_date");
		}
		
		if (order != null && order.equals("isUnPrice")) {
			order = "p.price ASC, p.idProduct DESC";
			request.setAttribute("order", "&order=isUnPrice");
		} else if (order != null && order.equals("isPrice")) {
			order = "p.price DESC, p.idProduct DESC";
			request.setAttribute("order", "&order=isPrice");
		}
		
		String isDisplay_str = request.getParameter("isDisplay");
		String id_str = request.getParameter("id");
		if (id_str != null && isDisplay_str != null) {
			int id = Integer.parseInt(id_str);
			int isDisplay = Integer.parseInt(isDisplay_str);
			
			if (isDisplay == 1) {
				isDisplay = 0;
			} else if (isDisplay == 0) {
				isDisplay = 1;
			}
			
			ProductCMD cmd = new ProductCMD(id, isDisplay);
			try {
				ProductDAO.getInstance().unActivityProduct(cmd);
				request.setAttribute("alert", "<script type='text/javascript'>alert('처리가 완료되었습니다.')</script>");
			} catch (Exception e) {
				DBHandler.getInstance().disconnect();
				e.printStackTrace();
			}
		}
		
		String page_str = request.getParameter("page");
		int page = 0; 
		if (page_str == null) {
			page = 1;
		} else {
			page = Integer.parseInt(page_str);
		}
		
		String pageCnt_str = request.getParameter("pageCnt");
		int pageCnt = 0;
		if (pageCnt_str == null || pageCnt_str.equals("")) {
			pageCnt = 10;
		} else {
			pageCnt = Integer.parseInt(pageCnt_str);
		}
		
		try {
			if (!where.equals("")) request.setAttribute("where", "&where=" + URLEncoder.encode(where, "UTF-8"));
			
			request.setAttribute("brand", BrandDAO.getInstance().getBrandList(1, -1, "brandTitle", null));
			request.setAttribute("first", FirstCategoryDAO.getInstance().getFirstCategoryList(1, -1, null));
			
			request.setAttribute("page", PageHandler.getInstance());
			request.setAttribute("pageCnt", pageCnt);
			request.setAttribute("product", ProductDAO.getInstance().getProductList(page, pageCnt, order, where));
			if (where.equals("")) {
				request.setAttribute("display", DBHandler.getInstance().getCount("product", "idProduct", "isDisplay='1'"));
				request.setAttribute("unDisplay", DBHandler.getInstance().getCount("product", "idProduct", "isDisplay='0'"));
			} else {
				request.setAttribute("display", DBHandler.getInstance().getCount("product p", "idProduct", "isDisplay='1'" + where));
				request.setAttribute("unDisplay", DBHandler.getInstance().getCount("product p", "idProduct", "isDisplay='0'" + where));
			}
		} catch (Exception e) {
			DBHandler.getInstance().disconnect();
			e.printStackTrace();
		}
		
		DBHandler.getInstance().disconnect();
		RequestDispatcher dpr = request.getRequestDispatcher("/product/listProduct.jsp");
		dpr.forward(request, response);
	}

	/** 제품등록으로 처리되는 구간 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		DBHandler.getInstance().connect();
		
		request.setCharacterEncoding("utf-8");
		
		int idBrand = 0;
		String sql = "SELECT idBrand FROM product ORDER BY create_date DESC LIMIT 0, 1";
		PreparedStatement pstmt = DBHandler.getInstance().getPrepareStatement(sql);
		try {
			ResultSet rs = pstmt.executeQuery();
			if (rs != null) {
				if (rs.next()) {
					idBrand = rs.getInt(1);
				}
				rs.close();
				rs = null;
			}
			pstmt.close();
			pstmt = null;
		} catch (SQLException e) {}
		request.setAttribute("searchBrand", idBrand);
		
		MultipartBox mbox = MultipartBox.getInstance();
		
		FileInfo finfo = mbox.upload2(request);
		
		int brand = mbox.getInt("brand");
		String productTitle = mbox.getValue("productTitle");
		String[] second = mbox.getValues("second");
		String[] keyWord_arr = mbox.getValues("keyWord");
		String volume = mbox.getValue("volume");
		int price = mbox.getInt("price");
		String colorType = mbox.getValue("colorType");
		if (colorType.equals("")) {
			colorType = null;
		}
		String productText = mbox.getValue("productText");
		
		String keyWord = null;
		if (keyWord_arr != null) {
			keyWord = "";
			ArrayList<String> list = new ArrayList<String>();
			for (int i=0; i<keyWord_arr.length; i++) {
				if (!list.contains(keyWord_arr[i])) {
					list.add(keyWord_arr[i]);
				}
			}
			String uniqueKeyWord_arr[] = new String[list.size()];
			list.toArray(uniqueKeyWord_arr);
			for (int i=0; i<uniqueKeyWord_arr.length; i++) {
				if (i+1 == uniqueKeyWord_arr.length) {
					keyWord += uniqueKeyWord_arr[i];
				} else {
					keyWord += uniqueKeyWord_arr[i] + ",";
				}
			}
		}
		
		int[] n_second = new int[second.length];
		for (int i=0; i<second.length; i++) {
			n_second[i] = Integer.parseInt(second[i]);
		}
		
		ArrayList<ProductCategoryCMD> cateList = new ArrayList<ProductCategoryCMD>();
		for (int i=0; i<n_second.length; i++) {
			ProductCategoryCMD cmd = new ProductCategoryCMD(n_second[i]);
			cateList.add(cmd);
		}
		
		try {
			ProductDAO.getInstance().insertProduct(new ProductCMD(brand, productTitle, volume, price, productText, colorType, keyWord, finfo), cateList);
		} catch (Exception e) {
			DBHandler.getInstance().disconnect();
			mbox.deleteFile(finfo);
			e.printStackTrace();
		}
		
		String where = request.getParameter("where");
		if (where != null) {
			where = Util.getInstance().incoding(where);
		}
		
		String order = request.getParameter("order");
		request.setAttribute("sort", order);
		
		if (order != null && order.equals("isUnDisplay")) {
			order = "p.isDisplay ASC, p.idProduct DESC";
			request.setAttribute("order", "&order=isUnDisplay");
		} else if (order != null && order.equals("isDisplay")) {
			order = "p.isDisplay DESC, p.idProduct DESC";
			request.setAttribute("order", "&order=isDisplay");
		}
		
		if (order != null && order.equals("isUnReadCount")) {
			order = "p.readCount ASC, p.idProduct DESC";
			request.setAttribute("order", "&order=isUnReadCount");
		} else if (order != null && order.equals("isReadCount")) {
			order = "p.readCount DESC, p.idProduct DESC";
			request.setAttribute("order", "&order=isReadCount");
		}
		
		if (order != null && order.equals("isUnWishCount")) {
			order = "count1 ASC, p.idProduct DESC";
			request.setAttribute("order", "&order=isUnWishCount");
		} else if (order != null && order.equals("isWishCount")) {
			order = "count1 DESC, p.idProduct DESC";
			request.setAttribute("order", "&order=isWishCount");
		}
		
		if (order != null && order.equals("isUnPouchCount")) {
			order = "count2 ASC, p.idProduct DESC";
			request.setAttribute("order", "&order=isUnPouchCount");
		} else if (order != null && order.equals("isPouchCount")) {
			order = "count2 DESC, p.idProduct DESC";
			request.setAttribute("order", "&order=isPouchCount");
		}
		
		if (order != null && order.equals("isUnCommentCount")) {
			order = "count3 ASC, p.idProduct DESC";
			request.setAttribute("order", "&order=isUnCommentCount");
		} else if (order != null && order.equals("isCommentCount")) {
			order = "count3 DESC, p.idProduct DESC";
			request.setAttribute("order", "&order=isCommentCount");
		}
		
		if (order != null && order.equals("isProductScore")) {
			order = "p.productScore DESC";
			request.setAttribute("order", "&order=isProductScore");
		}
		
		if (order != null && order.equals("isCreate_date")) {
			order = "p.create_date DESC";
			request.setAttribute("order", "&order=isCreate_date");
		}
		
		if (order != null && order.equals("isUnPrice")) {
			order = "p.price ASC, p.idProduct DESC";
			request.setAttribute("order", "&order=isUnPrice");
		} else if (order != null && order.equals("isPrice")) {
			order = "p.price DESC, p.idProduct DESC";
			request.setAttribute("order", "&order=isPrice");
		}
		
		ProductDAO dao = ProductDAO.getInstance();
		
		String isDisplay_str = request.getParameter("isDisplay");
		String id_str = request.getParameter("id");
		if (id_str != null && isDisplay_str != null) {
			int id = Integer.parseInt(id_str);
			int isDisplay = Integer.parseInt(isDisplay_str);
			
			if (isDisplay == 1) {
				isDisplay = 0;
			} else if (isDisplay == 0) {
				isDisplay = 1;
			}
			
			ProductCMD cmd = new ProductCMD(id, isDisplay);
			try {
				dao.unActivityProduct(cmd);
				request.setAttribute("alert", "<script type='text/javascript'>alert('처리가 완료되었습니다.')</script>");
			} catch (Exception e) {
				DBHandler.getInstance().disconnect();
				e.printStackTrace();
			}
		}
		
		String page_str = request.getParameter("page");
		int page = 0; 
		if (page_str == null) {
			page = 1;
		} else {
			page = Integer.parseInt(page_str);
		}
		
		try {
			if (where != null) request.setAttribute("where", "&where=" + URLEncoder.encode(where, "UTF-8"));
			
			request.setAttribute("brand", BrandDAO.getInstance().getBrandList(1, -1, "brandTitle", null));
			request.setAttribute("first", FirstCategoryDAO.getInstance().getFirstCategoryList(1, -1, null));
			
			request.setAttribute("page", PageHandler.getInstance());
			request.setAttribute("product", dao.getProductList(page, 10, order, where));
			if (where == null) {
				request.setAttribute("display", DBHandler.getInstance().getCount("product", "idProduct", "isDisplay='1'"));
				request.setAttribute("unDisplay", DBHandler.getInstance().getCount("product", "idProduct", "isDisplay='0'"));
			} else {
				request.setAttribute("display", DBHandler.getInstance().getCount("product p", "idProduct", "isDisplay='1'" + where));
				request.setAttribute("unDisplay", DBHandler.getInstance().getCount("product p", "idProduct", "isDisplay='0'" + where));
			}
		} catch (Exception e) {
			DBHandler.getInstance().disconnect();
			e.printStackTrace();
		}
		
		DBHandler.getInstance().disconnect();
		RequestDispatcher dpr = request.getRequestDispatcher("/product/listProduct.jsp");
		dpr.forward(request, response);
	}

}